﻿using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using WebApi_SY.Entity;
using WebApi_SY.Models;
using ClosedXML.Excel;  // 新增命名空间

using System.Text.RegularExpressions;

using System.Text;
using Xceed.Words.NET;
using System.Net.Http.Headers;
using System.Configuration;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using DocumentFormat.OpenXml.Wordprocessing;
using OfficeOpenXml;
using iTextSharp;
using iTextSharp.text.pdf;
using System.Web.Hosting;
using Spire.Xls;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Threading;
using System.Drawing;
using System.Globalization;
using System.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Reflection;
using DocumentFormat.OpenXml.Office2010.Excel;
using System.Runtime.Serialization;
using System.Security.AccessControl;
using Microsoft.Identity.Client;
using System.Security.Claims;

using Workbook = Spire.Xls.Workbook;
using Microsoft.Graph;
using Image = System.Drawing.Image;
using File = System.IO.File;
using Directory = System.IO.Directory;
using System.Runtime.Remoting;
using iTextSharp.text.pdf.qrcode;
using ZXing;
using ZXing.QrCode;
using Microsoft.AspNetCore.Routing.Template;


//useing Wintellect Web API

namespace WebApi_SY.Controllers
{

    public class Sli_document_mp_forgeController : ApiController
    {
        [HttpPost]
        public async Task<object> mp_forge_Insert([FromBody] sli_document_mp_forge forge)
        {
            var context = new YourDbContext();
            try
            {
                if (forge == null)
                {
                    throw new ArgumentNullException(nameof(forge), "传入的 forge 对象不能为 null。");
                }

                var newForge = new sli_document_mp_forge
                {
                    Fworkorder = forge.Fworkorder ?? "默认工单号",
                    Fqty = forge.Fqty ?? 0,
                    Fdeliverydate = forge.Fdeliverydate ?? DateTime.Now,
                    Fnumber = forge.Fnumber ?? "默认编号",
                    Fversion = forge.Fversion ?? "1.0",
                    Fproducttype = forge.Fproducttype ?? "默认类型",
                    Fstatus = forge.Fstatus ?? 0,
                    Fbiller = forge.Fbiller ?? 0,
                    Fsliouterdiameter = forge.Fsliouterdiameter ?? 0,
                    Fsliinnerdiameter = forge.Fsliinnerdiameter ?? 0,
                    Fslihight = forge.Fslihight ?? 0,
                    Fsliallowanceod = forge.Fsliallowanceod ?? 0,
                    Fsliallowanceid = forge.Fsliallowanceid ?? 0,
                    Fsliallowanceh = forge.Fsliallowanceh ?? 0,
                    Fsliallowanceodf = forge.Fsliallowanceodf ?? 0,
                    Fsliallowanceidf = forge.Fsliallowanceidf ?? 0,
                    Fsliallowancehf = forge.Fsliallowancehf ?? 0,
                    Fsliweightcontract = forge.Fsliweightcontract ?? 0,
                    Fsliweightmaterial = forge.Fsliweightmaterial ?? 0,
                    Fsliweightforging = forge.Fsliweightforging ?? 0,
                    Fsliweightgoods = forge.Fsliweightgoods ?? 0,
                    Fsliweightfurnace = forge.Fsliweightfurnace ?? 0,
                    Fslidrawingno = forge.Fslidrawingno ?? "默认图号",
                    Fslimetal = forge.Fslimetal ?? "默认金属材料",
                    Fheattreatment = forge.Fheattreatment ?? 0,
                    Fcooldown = forge.Fcooldown ?? 0,
                    Fslipf = forge.Fslipf ?? string.Empty,
                    Fslipm = forge.Fslipm ?? string.Empty,
                    Fsliph = forge.Fsliph ?? string.Empty,
                    Fslicnc = forge.Fslicnc ?? string.Empty,

                    Fprocess1 = forge.Fprocess1 ?? string.Empty,
                    Fprocess2 = forge.Fprocess2 ?? string.Empty,
                    Fprocess3 = forge.Fprocess3 ?? string.Empty,
                    Fprocess4 = forge.Fprocess4 ?? string.Empty,
                    Fprocess5 = forge.Fprocess5 ?? string.Empty,
                    Fprocess6 = forge.Fprocess6 ?? string.Empty,
                    F_slirouteing = forge.F_slirouteing ?? string.Empty,  // 默认工艺路线
                    F_sliequipment = forge.F_sliequipment ?? string.Empty, // 默认碾环机 (生产线 1,2,3)

                    Fhnormalizing = forge.Fhnormalizing ?? string.Empty, //正火
                    Fhtempering = forge.Fhtempering ?? string.Empty, // 回火
                    Fhannealing = forge.Fhannealing ?? string.Empty, // 退火
                    Fhqt = forge.Fhqt ?? string.Empty,  // 调质
                    Fhst = forge.Fhst ?? string.Empty, // 固溶
                    Fhnone = forge.Fhnone ?? string.Empty // 无热处理


                };

                context.sli_document_mp_forge.Add(newForge);
                await context.SaveChangesAsync();

                return new
                {
                    code = 200,
                    msg = "Success",
                    data = newForge.Id.ToString() + "保存成功"
                };
            }
            catch (Exception ex)
            {
                return new
                {
                    code = 500,
                    msg = "失败",
                    data = ex.ToString()
                };
            }
        }




        [HttpPost]
        public async Task<object> mp_forge_Update([FromBody] sli_document_mp_forge objct)
        {
            try
            {
                var context = new YourDbContext();
                var entity = await context.sli_document_mp_forge.FindAsync(objct.Id);

                if (entity == null)
                {
                    return new
                    {
                        code = 404,
                        msg = "记录不存在"
                    };
                }

                // 更新所有可修改字段
                entity.Fworkorder = objct.Fworkorder ?? entity.Fworkorder;
                entity.Fqty = objct.Fqty ?? entity.Fqty;
                entity.Fdeliverydate = objct.Fdeliverydate ?? entity.Fdeliverydate;
                entity.Fnumber = objct.Fnumber ?? entity.Fnumber;
                entity.Fversion = objct.Fversion ?? entity.Fversion;
                entity.Fproducttype = objct.Fproducttype ?? entity.Fproducttype;
                entity.Fstatus = objct.Fstatus ?? entity.Fstatus;
                entity.Fbiller = objct.Fbiller ?? entity.Fbiller;
                entity.Fsliouterdiameter = objct.Fsliouterdiameter ?? entity.Fsliouterdiameter;
                entity.Fsliinnerdiameter = objct.Fsliinnerdiameter ?? entity.Fsliinnerdiameter;
                entity.Fslihight = objct.Fslihight ?? entity.Fslihight;
                entity.Fsliallowanceod = objct.Fsliallowanceod ?? entity.Fsliallowanceod;
                entity.Fsliallowanceid = objct.Fsliallowanceid ?? entity.Fsliallowanceid;
                entity.Fsliallowanceh = objct.Fsliallowanceh ?? entity.Fsliallowanceh;
                entity.Fsliallowanceodf = objct.Fsliallowanceodf ?? entity.Fsliallowanceodf;
                entity.Fsliallowanceidf = objct.Fsliallowanceidf ?? entity.Fsliallowanceidf;
                entity.Fsliallowancehf = objct.Fsliallowancehf ?? entity.Fsliallowancehf;
                entity.Fsliweightcontract = objct.Fsliweightcontract ?? entity.Fsliweightcontract;
                entity.Fsliweightmaterial = objct.Fsliweightmaterial ?? entity.Fsliweightmaterial;
                entity.Fsliweightforging = objct.Fsliweightforging ?? entity.Fsliweightforging;
                entity.Fsliweightgoods = objct.Fsliweightgoods ?? entity.Fsliweightgoods;
                entity.Fsliweightfurnace = objct.Fsliweightfurnace ?? entity.Fsliweightfurnace;
                entity.Fslidrawingno = objct.Fslidrawingno ?? entity.Fslidrawingno;
                entity.Fslimetal = objct.Fslimetal ?? entity.Fslimetal;
                entity.Fheattreatment = objct.Fheattreatment ?? entity.Fheattreatment;
                entity.Fcooldown = objct.Fcooldown ?? entity.Fcooldown;

                entity.Fslipf = objct.Fslipf ?? entity.Fslipf;
                entity.Fslipm = objct.Fslipm ?? entity.Fslipm;
                entity.Fsliph = objct.Fsliph ?? entity.Fsliph;
                entity.Fslicnc = objct.Fslicnc ?? entity.Fslicnc;

                entity.Fprocess1 = objct.Fprocess1 ?? entity.Fprocess1;
                entity.Fprocess2 = objct.Fprocess2 ?? entity.Fprocess2;
                entity.Fprocess3 = objct.Fprocess3 ?? entity.Fprocess3;
                entity.Fprocess4 = objct.Fprocess4 ?? entity.Fprocess4;
                entity.Fprocess5 = objct.Fprocess5 ?? entity.Fprocess5;
                entity.Fprocess6 = objct.Fprocess6 ?? entity.Fprocess6;

                entity.F_slirouteing = objct.F_slirouteing ?? entity.F_slirouteing;
                entity.F_sliequipment = objct.F_sliequipment ?? entity.F_sliequipment;


                await context.SaveChangesAsync();

                return new
                {
                    code = 200,
                    msg = "修改成功",
                    data = entity.Id
                };
            }
            catch (Exception ex)
            {
                return new
                {
                    code = 500,
                    msg = "更新失败",
                    data = ex.Message
                };
            }
        }



        [HttpPost]
        public async Task<object> mp_forge_BatchUpdateFromExcelss([FromBody] BulkExcelUpdateRequest request)
        {
            var results = new List<object>();

            foreach (var id in request.Ids)
            {
                try
                {
                    using (var context = new YourDbContext())
                    {
                        // 查找实体
                        var entity = await context.sli_document_mp_forge.FindAsync(id);
                        if (entity == null)
                        {
                            results.Add(new { Id = id, code = 404, msg = "记录不存在" });
                            continue;
                        }

                        // 验证必要字段存在
                        if (string.IsNullOrWhiteSpace(entity.Fslipf) || string.IsNullOrWhiteSpace(entity.Fworkorder))
                        {
                            results.Add(new { Id = id, code = 400, msg = "工艺文件编号或工作令号缺失" });
                            continue;
                        }

                        // 生成带工作令号的目录路径
                        var fworkorder = entity.Fworkorder.Trim();
                        var fworkorderPrefix = fworkorder.Length >= 4
                            ? fworkorder.Substring(0, 4)
                            : fworkorder.PadRight(4, '0').Substring(0, 4);

                        string basePath = @"C:\模拟工艺单_锻造";
                        string directoryPath = Path.Combine(basePath, fworkorderPrefix);

                        // 生成文件名和完整路径
                        var fileName = $"{fworkorder}.xlsx";
                        var fullPath = Path.Combine(directoryPath, fileName);
                        //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                        //ExcelPackage.License.SetNonCommercialOrganization("sliooo");
                        // 验证文件存在
                        if (!File.Exists(fullPath))
                        {
                            results.Add(new
                            {
                                Id = id,
                                code = 400,
                                msg = $"工艺文件不存在，文件名：{fileName}",
                                requiredFormat = "[Fslipf值]-[Fworkorder值].xlsx（存放在[Fworkorder前四位]子目录下）"
                            });
                            continue;
                        }

                        // 读取Excel文件
                        using (var package = new ExcelPackage(new FileInfo(fullPath)))
                        {
                            var worksheet = package.Workbook.Worksheets.FirstOrDefault();
                            if (worksheet == null)
                            {
                                results.Add(new { Id = id, code = 400, msg = "无效的Excel文件格式" });
                                continue;
                            }

                            // 带默认值的更新逻辑
                            entity.Fsliallowanceod = TryParseDecimal(worksheet.Cells["Z14"].Value) ?? entity.Fsliallowanceod;
                            entity.Fsliallowanceid = TryParseDecimal(worksheet.Cells["Z15"].Value) ?? entity.Fsliallowanceid;
                            entity.Fsliallowanceh = TryParseDecimal(worksheet.Cells["Z16"].Value) ?? entity.Fsliallowanceh;

                            entity.Fsliweightforging = TryParseDecimal(worksheet.Cells["F13"].Value) ?? entity.Fsliweightforging;
                            entity.Fsliweightmaterial = TryParseDecimal(worksheet.Cells["E18"].Value) ?? entity.Fsliweightmaterial;
                            entity.Fsliallowanceh = TryParseDecimal(worksheet.Cells["Z16"].Value) ?? entity.Fsliallowanceh;

                            entity.Fx = TryParseDecimal(worksheet.Cells["Z17"].Value) ?? entity.Fx;
                            entity.Fsliweightcontract = TryParseDecimal(worksheet.Cells["E17"].Value) ?? entity.Fsliweightcontract;
                            entity.F_sliequipment = worksheet.Cells["X9"].Value?.ToString() ?? string.Empty;
                        }

                        await context.SaveChangesAsync();

                        results.Add(new
                        {
                            Id = id,
                            code = 200,
                            msg = "Excel数据更新成功",
                            data = new
                            {
                                FileName = fileName,
                                UpdatedFields = new[] {
                    nameof(entity.Fsliallowanceod),
                    nameof(entity.Fsliallowanceid),
                    nameof(entity.Fsliallowanceh),
                    nameof(entity.F_sliequipment)
                }
                            }
                        });
                    }
                }
                catch (Exception ex)
                {
                    results.Add(new
                    {
                        Id = id,
                        code = 500,
                        msg = "处理过程中发生异常",
                        error = ex.Message
                    });
                }
            }
            // 统计处理结果
            var successCount = results.Count(r => (r as dynamic).code == 200);
            return new
            {
                code = 200,
                msg = $"批量处理完成，共处理{request.Ids.Count}条，成功{successCount}条，失败{request.Ids.Count - successCount}条",
                results
            };
        }

        // 优化后的数值解析方法
        private decimal? TryParseDecimal(object cellValue)
        {
            if (cellValue == null) return null;

            var strValue = cellValue.ToString()
                .Replace(",", "")    // 移除千分位符号
                .Replace(" ", "");   // 移除空格

            if (decimal.TryParse(strValue, NumberStyles.Any, CultureInfo.InvariantCulture, out decimal result))
            {
                return result;
            }
            return null;
        }

        // 数值解析方法（增强容错）
        private decimal? TryParseDecimal(string value)
        {
            if (string.IsNullOrWhiteSpace(value)) return null;

            // 移除可能干扰字符（如货币符号、千分位逗号）
            var cleanValue = value.Replace(",", "").Replace("￥", "").Replace("$", "");

            if (decimal.TryParse(cleanValue, NumberStyles.Any, CultureInfo.InvariantCulture, out decimal result))
            {
                return result;
            }
            return null;
        }



        public class ExcelUpdateRequest
        {
            public int Id { get; set; } // 只需要传ID即可
        }


        private int? ParseQuantityFromCell(string cellText)
        {
            if (string.IsNullOrWhiteSpace(cellText))
                return null;

            // 提取第一个数字部分（忽略括号内容）
            var match = Regex.Match(cellText, @"^(\d+)");
            if (match.Success && int.TryParse(match.Groups[1].Value, out int result))
            {
                return result;
            }
            return null;
        }


        //-----------------
        [HttpPost]
        public async Task<object> mp_forge_BatchUpdateFromExcels([FromBody] BulkExcelUpdateRequest request)
        {
            var results = new List<object>();

            foreach (var id in request.Ids)
            {
                try
                {
                    using (var context = new YourDbContext())
                    {
                        // 查找实体
                        var entity = await context.sli_document_mp_forge.FindAsync(id);
                        if (entity == null)
                        {
                            results.Add(new { Id = id, code = 404, msg = "记录不存在" });
                            continue;
                        }

                        // 验证必要字段
                        if ( string.IsNullOrWhiteSpace(entity.Fworkorder))
                        {
                            results.Add(new { Id = id, code = 400, msg = "工艺文件编号或工作令号缺失" });
                            continue;
                        }

                        // 生成文件路径
                        var fworkorder = entity.Fworkorder.Trim();
                        var fworkorderPrefix = fworkorder.Length >= 4
                            ? fworkorder.Substring(0, 4)
                            : fworkorder.PadRight(4, '0').Substring(0, 4);

                        string basePath = @"C:\模拟工艺单_锻造\工艺单\6月份";
                        //string directoryPath = Path.Combine(basePath, fworkorderPrefix);
                        var fileName = $"{fworkorder}.xlsx";
                        var fullPath = Path.Combine(basePath, fileName);

                        if (!File.Exists(fullPath))
                        {
                            results.Add(new { Id = id, code = 400, msg = $"工艺文件不存在，文件名：{fileName}", requiredFormat = "[Fslipf值]-[Fworkorder值].xlsx（存放在[Fworkorder前四位]子目录下）" });
                            continue;
                        }

                        // 读取Excel并计算公式
                        var workbook = new Spire.Xls.Workbook();
                        workbook.LoadFromFile(fullPath, true); // 第三个参数启用公式计算
                        //workbook.LoadFromFile(fullPath);
                        workbook.CalculateAllValue(); // 关键修改：强制计算公式
                        var worksheet = workbook.Worksheets[0];

                        //新增辅助方法获取单元格值
                        decimal? GetCellValue(string address)
                        {
                            var cell = worksheet.Range[address];
                            try
                            {
                                // 检查单元格是否包含公式
                                if (cell.HasFormula)
                                {
                                    if (cell.HasNumber)
                                    {
                                        return Math.Round(Convert.ToDecimal(cell.FormulaValue), 0);
                                    }
                                    // 如果不是数值，尝试解析文本内容
                                    return TryParseDecimal(cell.FormulaValue);
                                }
                                else
                                {
                                    // 普通单元格的处理逻辑
                                    return cell.HasNumber
                                        ? Convert.ToDecimal(cell.NumberValue)
                                        : TryParseDecimal(cell.Text);
                                }
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine($"读取单元格 {address} 失败: {ex.Message}");
                                return null;
                            }
                        }

                        // 安全取值方法（自动处理缺失列）
                        string GetStringValue(string address)
                        {
                            return GetCellValue(address)?.ToString();
                        }

                        var cellD13 = worksheet.Range["D13"];

                        // 使用示例
                        entity.Fnumber = Convert.ToString( cellD13.FormulaValue) ?? entity.Fnumber; //文本需要单独处理

                        entity.Fproducttype = GetStringValue("AE1") ?? entity.Fproducttype;
                        entity.Fslipf = GetStringValue("AF1") ?? entity.Fslipf;
                        entity.Ftemplatename = GetStringValue("AF1") ?? entity.Ftemplatename;

                        entity.Fslidrawingno = GetStringValue("D14") ?? entity.Fslidrawingno;

                        var cellD15 = worksheet.Range["D15"];   //文本需要单独处理
                        entity.Fslimetal = Convert.ToString(cellD15.FormulaValue) ?? entity.Fslimetal;
                        entity.Fsliouterdiameter = GetCellValue("X13") ?? entity.Fsliouterdiameter;
                        entity.Fsliinnerdiameter = GetCellValue("Z13") ?? entity.Fsliinnerdiameter;
                        entity.Fslihight = GetCellValue("AB13") ?? entity.Fslihight;
                        entity.Fsliweightcontract = GetCellValue("E17") ?? entity.Fsliweightcontract;  //合同重量(kg)‌Z15
                        entity.Fsliweightfurnace = GetCellValue("F15") ?? entity.Fsliweightfurnace;//火耗重量F14
                        entity.Fsliweightgoods = GetCellValue("F16") ?? entity.Fsliweightgoods;//成品重量F15

                        // 批量更新字段（所有数值字段使用新读取逻辑）
                        entity.Fsliallowanceod = GetCellValue("Z14") ?? entity.Fsliallowanceod;
                        entity.Fsliallowanceid = GetCellValue("Z15") ?? entity.Fsliallowanceid;
                        entity.Fsliallowanceh = GetCellValue("Z16") ?? entity.Fsliallowanceh;

                        // 批量更新字段（所有数值字段使用新读取逻辑）
                        entity.Fsliallowanceodf = GetCellValue("N18") ?? entity.Fsliallowanceodf;
                        entity.Fsliallowanceidf = GetCellValue("P18") ?? entity.Fsliallowanceidf;
                        entity.Fsliallowancehf = GetCellValue("S18") ?? entity.Fsliallowancehf; //

                        entity.Fsliweightforging = GetCellValue("F13") ?? entity.Fsliweightforging;
                        entity.Fsliweightmaterial = GetCellValue("E18") ?? entity.Fsliweightmaterial;  //下料重量

                        //entity.Fqty = GetCellValue("D16") ?? entity.Fqty;
                        var D16 = worksheet.Range["D16"];
                        string D16Sum = "";
                        if (D16.HasFormula)
                        {
                            D16Sum = Convert.ToString(D16.FormulaValue);
                           // return TryParseDecimal(cell.);
                        }
                        else
                        {
                            D16Sum = Convert.ToString(D16.Text);
                            // 普通单元格的处理逻辑

                        }



                        entity.Fqty = ParseQuantityFromCell(D16Sum) ?? entity.Fqty;

                        // Z16重复赋值可能是代码冗余，根据实际情况决定是否保留
                        entity.Fsliallowanceh = GetCellValue("Z16") ?? entity.Fsliallowanceh;

                        entity.Fx = GetCellValue("Z17") ?? entity.Fx;
                        entity.Fsliweightcontract = GetCellValue("E17") ?? entity.Fsliweightcontract;

                        // 处理文本字段（X9单元格）
                        //var x9Cell = GetCellValue("X9");
                        entity.F_sliequipment = GetCellValue("X9").ToString() ?? "";

                        //// 公式结果验证（示例：验证关键字段）
                        //if (worksheet.Range["E17"].HasFormula && entity.Fsliweightcontract == null)
                        //{
                        //    results.Add(new { Id = id, code = 400, msg = "E17单元格公式计算结果无效" });
                        //    continue;
                        //}

                        await context.SaveChangesAsync();

                        results.Add(new
                        {
                            Id = id,
                            code = 200,
                            msg = "Excel数据更新成功",
                            data = new
                            {
                                FileName = fileName,
                                UpdatedFields = new[] {
                            nameof(entity.Fsliallowanceod),
                            nameof(entity.Fsliallowanceid),
                            nameof(entity.Fsliallowanceh),
                            nameof(entity.Fsliweightcontract),
                            nameof(entity.Fsliweightforging),
                            nameof(entity.Fsliweightmaterial),
                            nameof(entity.F_sliequipment)
                        }
                            }
                        });
                    }
                }
                catch (Exception ex)
                {
                    results.Add(new { Id = id, code = 500, msg = "处理过程中发生异常", error = ex.Message });
                }
            }



            var successCount = results.Count(r => (r as dynamic).code == 200);
            return new
            {
                code = 200,
                msg = $"批量处理完成，共处理{request.Ids.Count}条，成功{successCount}条，失败{request.Ids.Count - successCount}条",
                results
            };
        }

        // 增强版数值解析方法



        // 新增的定时处理接口
        [HttpPost]
        public async Task<object> mp_forge_ProcessPendingRecords()
        {
            var results = new List<object>();

            try
            {
                using (var context = new YourDbContext())
                {
                    // 获取所有Fstatus=0的记录
                    var pendingRecords = await context.sli_document_mp_forge
                        .Where(e => e.Fstatus == 0)
                        .ToListAsync();

                    if (!pendingRecords.Any())
                    {
                        return new { code = 200, msg = "没有待处理的记录" };
                    }

                    foreach (var entity in pendingRecords)
                    {
                        try
                        {
                            // 验证必要字段
                            if (string.IsNullOrWhiteSpace(entity.Fworkorder))
                            {
                                results.Add(new { Id = entity.Id, code = 400, msg = "工艺文件编号或工作令号缺失" });
                                continue;
                            }

                            // 生成文件路径
                            var fworkorder = entity.Fworkorder.Trim();
                            var fworkorderPrefix = fworkorder.Length >= 4
                                ? fworkorder.Substring(0, 4)
                                : fworkorder.PadRight(4, '0').Substring(0, 4);

                            string basePath = @"C:\模拟工艺单_锻造";
                            string directoryPath = Path.Combine(basePath, fworkorderPrefix);
                            var fileName = $"{fworkorder}.xlsx";
                            var fullPath = Path.Combine(directoryPath, fileName);

                            if (!File.Exists(fullPath))
                            {
                                results.Add(new
                                {
                                    Id = entity.Id,
                                    code = 400,
                                    msg = $"工艺文件不存在，文件名：{fileName}",
                                    requiredFormat = "[Fslipf值]-[Fworkorder值].xlsx（存放在[Fworkorder前四位]子目录下）"
                                });
                                continue;
                            }

                            // 读取Excel并计算公式
                            var workbook = new Spire.Xls.Workbook();
                            workbook.LoadFromFile(fullPath, true);
                            workbook.CalculateAllValue();
                            var worksheet = workbook.Worksheets[0];

                            // 辅助方法获取单元格值
                            decimal? GetCellValue(string address)
                            {
                                var cell = worksheet.Range[address];
                                try
                                {
                                    if (cell.HasFormula)
                                    {
                                        return Math.Round(Convert.ToDecimal(cell.FormulaValue), 0);
                                    }
                                    else
                                    {
                                        return cell.HasNumber
                                            ? Convert.ToDecimal(cell.NumberValue)
                                            : TryParseDecimal(cell.Text);
                                    }
                                }
                                catch
                                {
                                    return null;
                                }
                            }

                            // 批量更新字段
                            entity.Fsliallowanceod = GetCellValue("Z14") ?? entity.Fsliallowanceod;
                            entity.Fsliallowanceid = GetCellValue("Z15") ?? entity.Fsliallowanceid;
                            entity.Fsliallowanceh = GetCellValue("Z16") ?? entity.Fsliallowanceh;
                            entity.Fsliallowanceodf = GetCellValue("N18") ?? entity.Fsliallowanceodf;
                            entity.Fsliallowanceidf = GetCellValue("P18") ?? entity.Fsliallowanceidf;
                            entity.Fsliallowancehf = GetCellValue("S18") ?? entity.Fsliallowancehf;
                            entity.Fsliweightforging = GetCellValue("F13") ?? entity.Fsliweightforging;
                            entity.Fsliweightmaterial = GetCellValue("E18") ?? entity.Fsliweightmaterial;
                            entity.Fqty = ParseQuantityFromCell(worksheet.Range["D16"].Text) ?? entity.Fqty;
                            entity.Fx = GetCellValue("Z17") ?? entity.Fx;
                            entity.Fsliweightcontract = GetCellValue("E17") ?? entity.Fsliweightcontract;
                            entity.F_sliequipment = GetCellValue("X9").ToString() ?? "";

                            // 更新状态为已处理
                            entity.Fstatus = 1;

                            results.Add(new
                            {
                                Id = entity.Id,
                                code = 200,
                                msg = "Excel数据更新成功",
                                data = new
                                {
                                    FileName = fileName,
                                    UpdatedFields = new[] {
                                nameof(entity.Fsliallowanceod),
                                nameof(entity.Fsliallowanceid),
                                nameof(entity.Fsliallowanceh),
                                nameof(entity.Fsliweightcontract),
                                nameof(entity.Fsliweightforging),
                                nameof(entity.Fsliweightmaterial),
                                nameof(entity.F_sliequipment)
                            }
                                }
                            });
                        }
                        catch (Exception ex)
                        {
                            results.Add(new
                            {
                                Id = entity.Id,
                                code = 500,
                                msg = "处理过程中发生异常",
                                error = ex.Message
                            });
                        }
                    }

                    await context.SaveChangesAsync();
                }

                return new
                {
                    code = 200,
                    msg = "批量处理完成",
                    totalCount = results.Count,
                    successCount = results.Count(r => (r as dynamic).code == 200),
                    details = results
                };
            }
            catch (Exception ex)
            {
                return new
                {
                    code = 500,
                    msg = "系统处理过程中发生异常",
                    error = ex.Message
                };
            }
        }


        //----------------***********************
   

        //------------------------

        [HttpPost]
        public async Task<object> mp_forge_Delete(List<int> ids)
        {
            try
            {
                var context = new YourDbContext();
                var recordsToDelete = context.sli_document_mp_forge.Where(x => ids.Contains(x.Id)).ToList();

                if (!recordsToDelete.Any())
                {
                    return new
                    {
                        code = 404,
                        msg = "未找到指定记录"
                    };
                }

                context.sli_document_mp_forge.RemoveRange(recordsToDelete);
                await context.SaveChangesAsync();

                return new
                {
                    code = 200,
                    msg = "删除成功",
                    data = string.Join(",", ids)
                };
            }
            catch (Exception ex)
            {
                return new
                {
                    code = 500,
                    msg = "删除失败",
                    data = ex.Message
                };
            }
        }

        [HttpGet]
        [Route("api/sli_document_mp_forge/CheckForge")]
        public IHttpActionResult sli_mp_check(int Id = 1, int Fstatus = 1)
        {
            try
            {
                using (var context = new YourDbContext())
                {
                    // 使用Microsoft.Data.SqlClient命名空间的SqlParameter
                    var fidParam = new Microsoft.Data.SqlClient.SqlParameter("@Id", Id);

                    // 显式指定参数类型（推荐）
                    fidParam.SqlDbType = SqlDbType.Int;

                    // 使用Microsoft.Data.SqlClient命名空间的SqlParameter
                    var Fstatusp = new Microsoft.Data.SqlClient.SqlParameter("@Fstatus", Fstatus);

                    // 显式指定参数类型（推荐）
                    Fstatusp.SqlDbType = SqlDbType.Int;

                    // 执行存储过程（EF Core 5.0+推荐方式）
                    var rowsAffected = context.Database.ExecuteSqlRaw(
                        "EXEC sli_document_mp_forge_check @Id,@Fstatus",
                        fidParam, Fstatusp);

                    return Ok(new
                    {
                        code = 200,
                        msg = "检查完成",
                        data = new { affectedRows = rowsAffected }
                    });
                }
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "检查过程中发生错误",
                    data = ex.Message
                });
            }
        }

        [HttpGet]
        public IHttpActionResult GetForgeRecords(
     int page = 1,
     int pageSize = 10,
     string fnumber = null,
     string fworkorder = null,
     string Ftemplatename = null, // 新增模板名称参数
     int? Fstatus = null // 新增模板名称参数
 )
        {
            try
            {
                var context = new YourDbContext();
                IQueryable<sli_document_mp_forge> query = context.sli_document_mp_forge;

                // 按编号筛选
                if (!string.IsNullOrWhiteSpace(fnumber))
                {
                    query = query.Where(q => q.Fnumber.Contains(fnumber));
                }

                // 工作令号  筛选
                if (!string.IsNullOrWhiteSpace(fworkorder))
                {
                    query = query.Where(q => q.Fworkorder.Contains(fworkorder));
                }

                // 按模板名称筛选（假设实体类中存在 TemplateName 属性）
                if (!string.IsNullOrWhiteSpace(Ftemplatename))
                {
                    query = query.Where(q => q.Ftemplatename == Ftemplatename);
                }
                // 按模板名称筛选（假设实体类中存在 TemplateName 属性）
                if (Fstatus.HasValue)
                {
                    query = query.Where(q => q.Fstatus == Fstatus.Value);
                }
                // 分页逻辑
                int totalCount = query.Count();
                int totalPages = (int)Math.Ceiling((double)totalCount / pageSize);

                var data = query.OrderByDescending(x => x.Id)
                               .Skip((page - 1) * pageSize)
                               .Take(pageSize)
                               .ToList();

                return Ok(new
                {
                    code = 200,
                    msg = "OK",
                    data = new
                    {
                        totalCount,
                        totalPages,
                        currentPage = page,
                        pageSize,
                        records = data
                    }
                });
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "查询失败",
                    data = ex.Message
                });
            }
        }


        [HttpGet]
        public IHttpActionResult GetForgeById(int id)
        {
            try
            {
                var context = new YourDbContext();
                var record = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);

                if (record == null)
                {
                    return Ok(new
                    {
                        code = 404,
                        msg = "记录不存在"
                    });
                }

                return Ok(new
                {
                    code = 200,
                    msg = "OK",
                    data = record
                });
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "查询失败",
                    data = ex.Message
                });
            }
        }


        //--------------------********************************************
        [HttpPost]
        [Route("api/sli_document_mp_forge/upload")]
        public async Task<IHttpActionResult> UploadExcel() // 改为异步方法
        {
            try
            {
                // 1. 检查请求是否包含文件
                if (!Request.Content.IsMimeMultipartContent())
                {
                    return Ok(new { code = 400, msg = "请求格式错误（需要 multipart/form-data）" });
                }

                // 2. 配置上传参数
                var uploadPath = @"C:\template";
                // 确保目录存在
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }
                var provider = new MultipartFormDataStreamProvider(uploadPath);

                // 3. 异步处理上传
                var result = await Request.Content.ReadAsMultipartAsync(provider);

                // 4. 检查是否有文件（修复点：改用 FileData.Count）
                if (result.FileData.Count == 0) // 原错误行修改此处
                {
                    return Ok(new { code = 400, msg = "未检测到上传文件" });
                }

                // 5. 处理第一个文件
                var fileData = result.FileData[0];
                var originalFileName = fileData.Headers.ContentDisposition.FileName.Trim('\"');
                var fileExt = Path.GetExtension(originalFileName).ToLower();

                // 6. 验证文件类型
                if (fileExt != ".xlsx")
                {
                    return Ok(new { code = 415, msg = "仅支持 .xlsx 格式文件" });
                }

                // 7. 构建目标路径
                var newFileName = $"{Guid.NewGuid():N}_{DateTime.Now:yyyyMMddHHmmss}{fileExt}";
                var savePath = Path.Combine(uploadPath, newFileName);

                // 8. 移动文件
                File.Move(fileData.LocalFileName, savePath);

                // 9. 返回成功响应
                return Ok(new
                {
                    code = 200,
                    msg = "文件上传成功",
                    data = new
                    {
                        original_name = originalFileName,
                        saved_path = savePath,
                        file_size = new FileInfo(savePath).Length
                    }
                });
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "文件上传失败",
                    data = ex.Message
                });
            }
        }


        //-------------******************************************************************************
        [HttpGet]
        [Route("api/sli_document_mp_forge/export")]
        public IHttpActionResult ExportExcel(int id, string Ftemplatename = "工艺卡-HWQ-NY",int Ftype=0)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(Ftemplatename))
                    return BadRequest("模板名称不能为空");

                using (var context = new YourDbContext())
                {
                    var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);
                    if (forgeData == null) return NotFound();

                    var templatePath = Path.Combine(@"C:\template", $"{Ftemplatename.Trim()}.xlsx");
                    if (!File.Exists(templatePath))
                        return Content(HttpStatusCode.NotFound, "模板文件不存在");

                    // 关键修正：禁用公式计算 + 加载选项
                    var loadOptions = new LoadOptions { RecalculateAllFormulas = false };
                    using (var workbook = new XLWorkbook(templatePath, loadOptions))
                    {
                        var worksheet = workbook.Worksheets.FirstOrDefault();
                        if (worksheet == null) return BadRequest("无效模板");

                        var usedRange = worksheet.RangeUsed();
                        foreach (var cell in usedRange.Cells()
                            .Where(c => c.DataType == XLDataType.Text &&
                                        !c.HasFormula &&
                                        c.GetString().StartsWith("$")))
                        {
                            string propName = cell.Value.ToString().Substring(1);
                            var property = forgeData.GetType().GetProperty(propName);
                            if (property == null) continue;

                            string value = property.GetValue(forgeData)?.ToString() ?? "";

                            // 处理合并单元格
                            if (cell.IsMerged())
                            {
                                cell.MergedRange().FirstCell().Value = value;
                            }
                            else
                            {
                                cell.Value = value;
                            }
                        }
                        // 生成带工作令号的目录路径
                        var fworkorderPrefix = !string.IsNullOrEmpty(forgeData.Fworkorder)
                            ? forgeData.Fworkorder.Length >= 4
                                ? forgeData.Fworkorder.Substring(0, 4)  // 取前四位
                                : forgeData.Fworkorder.PadRight(4, '0').Substring(0, 4)  // 不足四位补零
                            : "0000";  // 空值处理

                        //string directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);

                        string directoryPath = "";
                        if (Ftype == 1)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);
                        }
                        else if (Ftype == 2)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                        }
                        else if (Ftype == 3)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_热处理", fworkorderPrefix);
                        }
                        else if (Ftype == 4)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_加工程序", fworkorderPrefix);
                        }
                        //string templatePath = Path.Combine(directoryPath, Content);
                        Directory.CreateDirectory(directoryPath);  // 自动创建目录

                        string fworkorder = forgeData.Fworkorder ?? id.ToString();
                        string fileName = $"{fworkorder}.xlsx";
                        string savePath = Path.Combine(directoryPath, fileName);



                        // 生成唯一文件名
                        //string fworkorder = forgeData.Fworkorder ?? id.ToString();
                        //string fileName = $"{Ftemplatename}-{fworkorder}.xlsx";
                        //string savePath = Path.Combine(@"C:\模拟工艺单_锻造", fileName);

                        workbook.SaveAs(savePath);

                        return Ok(new
                        {
                            code = 200,
                            msg = "文件生成成功",
                            data = new
                            {
                                //original_name = originalFileName,
                                fileName = fileName,
                                file_size = new FileInfo(savePath).Length
                            }
                        });
                        //eturn Ok(new { Path = savePath });
                    }
                }
            }
            catch (IndexOutOfRangeException ex)
            {
                File.WriteAllText(@"C:\error.log", $"索引越界: {ex.StackTrace}");
                return InternalServerError(new Exception("模板格式异常，请检查合并单元格或公式"));
            }
            catch (Exception ex)
            {
                File.WriteAllText(@"C:\error.log", ex.ToString());
                return InternalServerError();
            }


        }

        //------------------******************
        [HttpPost]
        [Route("api/sli_document_mp_forge/batch-export")]
        public IHttpActionResult BatchExportExcel([FromBody] BatchExportRequest request)
        {
            var result = new List<object>();

            try
            {
                if (request?.Items == null || !request.Items.Any())
                    return BadRequest("请求参数不能为空");

                using (var context = new YourDbContext())
                {
                    foreach (var item in request.Items)
                    {
                        var response = new { Id = item.Id, Code = 200, Msg = "", Data = new object() };

                        try
                        {
                            // 处理模板名称
                            var templateName = string.IsNullOrWhiteSpace(item.Fslipf)
                                ? "工艺卡-HWQ-NY"
                                : item.Fslipf.Trim();

                            var templatePath = Path.Combine(@"C:\template", $"{templateName}.xlsx");
                            if (!File.Exists(templatePath))
                            {
                                result.Add(new { Id = item.Id, Code = 404, Msg = $"模板文件不存在: {templateName}" });
                                continue;
                            }

                            var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == item.Id);
                            if (forgeData == null)
                            {
                                result.Add(new { Id = item.Id, Code = 404, Msg = "未找到对应数据" });
                                continue;
                            }

                            // 新增状态更新逻辑
                            forgeData.Fstatus = 1;  // 更新状态值
                            context.SaveChanges();  // 立即保存状态变更

                            //using (var workbook = new XLWorkbook(templatePath, new LoadOptions { RecalculateAllFormulas = false }))

                            using (var workbook = new XLWorkbook(templatePath, new LoadOptions { RecalculateAllFormulas = false }))
                            {
                                var worksheet = workbook.Worksheets.FirstOrDefault();
                                if (worksheet == null)
                                {
                                    result.Add(new { Id = item.Id, Code = 400, Msg = "无效模板" });
                                    continue;
                                }

                                // 优化：只遍历包含文本的单元格
                                var textCells = worksheet.CellsUsed(c => c.DataType == XLDataType.Text &&
                                                           !c.HasFormula &&
                                                           c.GetString().StartsWith("$"));
                                foreach (var cell in textCells)
                                {
                                    string propName = cell.Value.ToString().Substring(1);
                                    var property = forgeData.GetType().GetProperty(propName);
                                    if (property == null) continue;

                                    object propertyValue = property.GetValue(forgeData);
                                    string value = FormatCellValue(property, propertyValue);

                                    // ==== 关键修改：只清除当前单元格的公式 ====
                                    if (cell.HasFormula)
                                    {
                                        try
                                        {
                                            // 清除公式但保留当前值
                                            var currentValue = cell.Value;
                                            cell.FormulaA1 = string.Empty; // 清除公式
                                            cell.Value = currentValue;     // 恢复值
                                        }
                                        catch
                                        {
                                            // 如果获取值失败，直接清除内容
                                            cell.Clear();
                                        }
                                    }

                                    if (cell.IsMerged())
                                    {
                                        cell.MergedRange().FirstCell().Value = value;
                                    }
                                    else
                                    {
                                        cell.Value = value;
                                    }
                                }

                                // ==== 关键修复：添加公式验证和清理 ====


                                // 生成带工作令号的目录路径
                                var fworkorderPrefix = !string.IsNullOrEmpty(forgeData.Fworkorder)
                                    ? forgeData.Fworkorder.Length >= 4
                                        ? forgeData.Fworkorder.Substring(0, 4)  // 取前四位
                                        : forgeData.Fworkorder.PadRight(4, '0').Substring(0, 4)  // 不足四位补零
                                    : "0000";  // 空值处理
                                string directoryPath = "";
                                if (item.Ftype == 1)
                                {
                                    directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);
                                }
                                else if (item.Ftype == 2)
                                {
                                    directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                                }
                                else if (item.Ftype == 3)
                                {
                                    directoryPath = Path.Combine(@"C:\模拟工艺单_热处理", fworkorderPrefix);
                                }
                                else if (item.Ftype == 4)
                                {
                                    directoryPath = Path.Combine(@"C:\模拟工艺单_加工程序", fworkorderPrefix);
                                }
                                Directory.CreateDirectory(directoryPath);  // 自动创建目录

                                string fworkorder = forgeData.Fworkorder ?? item.Id.ToString();
                                string fileName = $"{fworkorder}.xlsx";
                                string savePath = Path.Combine(directoryPath, fileName);

                                // ==== 增强异常处理 ====
                                try
                                {
                                    // 使用更安全的保存方式
                                    workbook.SaveAs(savePath, new SaveOptions
                                    {
                                        ValidatePackage = true,
                                        GenerateCalculationChain = false
                                    });
                                }
                                catch (IndexOutOfRangeException ex)
                                {
                                    // 特定处理ClosedXML公式错误
                                    string errorDetail = $"公式处理失败: {ex.Message}";
                                    File.AppendAllText(@"C:\batch_error.log", $"[ID:{item.Id}] {errorDetail}\n");

                                    // 尝试基本保存方式
                                    try
                                    {
                                        workbook.SaveAs(savePath, new SaveOptions
                                        {
                                            ValidatePackage = false,
                                            GenerateCalculationChain = false
                                        });
                                        errorDetail += " (已尝试非验证方式保存成功)";
                                    }
                                    catch
                                    {
                                        errorDetail += " (非验证方式保存失败)";
                                        throw;
                                    }
                                    finally
                                    {
                                        result.Add(new { Id = item.Id, Code = 501, Msg = errorDetail });
                                    }
                                }

                                result.Add(new
                                {
                                    Id = item.Id,
                                    Code = 200,
                                    Msg = "文件生成成功",
                                    Data = new
                                    {
                                        TemplateName = templateName,
                                        FileName = fileName,
                                        FileSize = new FileInfo(savePath).Length,
                                        SavePath = savePath // 返回完整存储路径
                                    }
                                });
                            }
                        }
                        catch (Exception ex)
                        {
                            string errorMsg = (ex is IndexOutOfRangeException)
                                ? "Excel公式处理错误"
                                : "处理过程中发生错误";

                            File.AppendAllText(@"C:\batch_error.log", $"[ID:{item.Id}] {ex}\n");
                            result.Add(new { Id = item.Id, Code = 500, Msg = errorMsg, Detail = ex.Message });
                        }
                    }
                }

                return Ok(new
                {
                    code = 200,
                    msg = "批量处理完成",
                    total = request.Items.Count,
                    success = result.Count(r => (r as dynamic).Code == 200),
                    Failed = result.Count(r => (r as dynamic).Code != 200),
                    data = result
                });
            }
            catch (Exception ex)
            {
                File.AppendAllText(@"C:\batch_error.log", $"[GLOBAL] {DateTime.Now}: {ex}\n");
                return InternalServerError(new Exception("批量处理发生全局异常", ex));
            }
        }
        //------------------******************


        //------------------******************
        [HttpPost]
        [Route("api/sli_document_mp_forge/batch-exportm")]
        public IHttpActionResult BatchExportExcelm([FromBody] BatchExportRequest request)
        {
            var result = new List<object>();

            try
            {
                if (request?.Items == null || !request.Items.Any())
                    return BadRequest("请求参数不能为空");

                using (var context = new YourDbContext())
                {
                    foreach (var item in request.Items)
                    {
                        var response = new { Id = item.Id, Code = 200, Msg = "", Data = new object() };

                        try
                        {
                            // 处理模板名称
                            var templateName = string.IsNullOrWhiteSpace(item.Fslipm)
                                ? "工艺卡-HWQ-NY"
                                : item.Fslipm.Trim();

                            var templatePath = Path.Combine(@"C:\template", $"{templateName}.xlsx");
                            if (!File.Exists(templatePath))
                            {
                                result.Add(new { Id = item.Id, Code = 404, Msg = $"模板文件不存在: {templateName}" });
                                continue;
                            }

                            var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == item.Id);
                            if (forgeData == null)
                            {
                                result.Add(new { Id = item.Id, Code = 404, Msg = "未找到对应数据" });
                                continue;
                            }

                            // 新增状态更新逻辑
                            forgeData.Fstatus = 1;  // 更新状态值
                            context.SaveChanges();  // 立即保存状态变更

                            //using (var workbook = new XLWorkbook(templatePath, new LoadOptions { RecalculateAllFormulas = false }))

                            using (var workbook = new XLWorkbook(templatePath, new LoadOptions { RecalculateAllFormulas = false }))
                            {
                                var worksheet = workbook.Worksheets.FirstOrDefault();
                                if (worksheet == null)
                                {
                                    result.Add(new { Id = item.Id, Code = 400, Msg = "无效模板" });
                                    continue;
                                }

                                // 优化：只遍历包含文本的单元格
                                var textCells = worksheet.CellsUsed(c => c.DataType == XLDataType.Text &&
                                                           !c.HasFormula &&
                                                           c.GetString().StartsWith("$"));
                                foreach (var cell in textCells)
                                {
                                    string propName = cell.Value.ToString().Substring(1);
                                    var property = forgeData.GetType().GetProperty(propName);
                                    if (property == null) continue;

                                    object propertyValue = property.GetValue(forgeData);
                                    string value = FormatCellValue(property, propertyValue);

                                    // ==== 关键修改：只清除当前单元格的公式 ====
                                    if (cell.HasFormula)
                                    {
                                        try
                                        {
                                            // 清除公式但保留当前值
                                            var currentValue = cell.Value;
                                            cell.FormulaA1 = string.Empty; // 清除公式
                                            cell.Value = currentValue;     // 恢复值
                                        }
                                        catch
                                        {
                                            // 如果获取值失败，直接清除内容
                                            cell.Clear();
                                        }
                                    }

                                    if (cell.IsMerged())
                                    {
                                        cell.MergedRange().FirstCell().Value = value;
                                    }
                                    else
                                    {
                                        cell.Value = value;
                                    }
                                }

                                // ==== 关键修复：添加公式验证和清理 ====


                                // 生成带工作令号的目录路径
                                var fworkorderPrefix = !string.IsNullOrEmpty(forgeData.Fworkorder)
                                    ? forgeData.Fworkorder.Length >= 4
                                        ? forgeData.Fworkorder.Substring(0, 4)  // 取前四位
                                        : forgeData.Fworkorder.PadRight(4, '0').Substring(0, 4)  // 不足四位补零
                                    : "0000";  // 空值处理
                                
                                string directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                                Directory.CreateDirectory(directoryPath);  // 自动创建目录

                                string fworkorder = forgeData.Fworkorder ?? item.Id.ToString();
                                string fileName = $"{fworkorder}.xlsx";
                                string savePath = Path.Combine(directoryPath, fileName);

                                // ==== 增强异常处理 ====
                                try
                                {
                                    // 使用更安全的保存方式
                                    workbook.SaveAs(savePath, new SaveOptions
                                    {
                                        ValidatePackage = true,
                                        GenerateCalculationChain = false
                                    });
                                }
                                catch (IndexOutOfRangeException ex)
                                {
                                    // 特定处理ClosedXML公式错误
                                    string errorDetail = $"公式处理失败: {ex.Message}";
                                    File.AppendAllText(@"C:\batch_error.log", $"[ID:{item.Id}] {errorDetail}\n");

                                    // 尝试基本保存方式
                                    try
                                    {
                                        workbook.SaveAs(savePath, new SaveOptions
                                        {
                                            ValidatePackage = false,
                                            GenerateCalculationChain = false
                                        });
                                        errorDetail += " (已尝试非验证方式保存成功)";
                                    }
                                    catch
                                    {
                                        errorDetail += " (非验证方式保存失败)";
                                        throw;
                                    }
                                    finally
                                    {
                                        result.Add(new { Id = item.Id, Code = 501, Msg = errorDetail });
                                    }
                                }

                                result.Add(new
                                {
                                    Id = item.Id,
                                    Code = 200,
                                    Msg = "文件生成成功",
                                    Data = new
                                    {
                                        TemplateName = templateName,
                                        FileName = fileName,
                                        FileSize = new FileInfo(savePath).Length,
                                        SavePath = savePath // 返回完整存储路径
                                    }
                                });
                            }
                        }
                        catch (Exception ex)
                        {
                            string errorMsg = (ex is IndexOutOfRangeException)
                                ? "Excel公式处理错误"
                                : "处理过程中发生错误";

                            File.AppendAllText(@"C:\batch_error.log", $"[ID:{item.Id}] {ex}\n");
                            result.Add(new { Id = item.Id, Code = 500, Msg = errorMsg, Detail = ex.Message });
                        }
                    }
                }

                return Ok(new
                {
                    code = 200,
                    msg = "批量处理完成",
                    total = request.Items.Count,
                    success = result.Count(r => (r as dynamic).Code == 200),
                    Failed = result.Count(r => (r as dynamic).Code != 200),
                    data = result
                });
            }
            catch (Exception ex)
            {
                File.AppendAllText(@"C:\batch_error.log", $"[GLOBAL] {DateTime.Now}: {ex}\n");
                return InternalServerError(new Exception("批量处理发生全局异常", ex));
            }
        }
        //------------------******************





        // 以下保持原有格式化方法不变
        private static string FormatCellValue(PropertyInfo property, object value)
        {
            if (value == null) return "";

            if (property.PropertyType == typeof(string))
            {
                if (decimal.TryParse(value.ToString(), out decimal decimalValue))
                {
                    return FormatNumber(decimalValue);
                }
                return value.ToString();
            }

            TypeCode typeCode = Type.GetTypeCode(property.PropertyType);
            switch (typeCode)
            {
                case TypeCode.Decimal:
                case TypeCode.Double:
                case TypeCode.Single:
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.UInt32:
                case TypeCode.UInt64:
                case TypeCode.Int16:
                case TypeCode.UInt16:
                case TypeCode.Byte:
                case TypeCode.SByte:
                    return FormatNumber(value);
                default:
                    return value.ToString();
            }
        }

        private static string FormatNumber(object number)
        {
            try
            {
                decimal decimalValue = Convert.ToDecimal(number);
                return decimalValue.ToString(decimalValue % 1 == 0 ? "0" : "0.0###########");
            }
            catch
            {
                if (number is string strValue && decimal.TryParse(strValue, out decimal parsedDecimal))
                {
                    return parsedDecimal.ToString(parsedDecimal % 1 == 0 ? "0" : "0.0###########");
                }
                return number.ToString();
            }
        }

        public class BatchExportRequest
        {
            public List<ExportItem> Items { get; set; }
 
        }

        public class ExportItem
        {
            public int Id { get; set; }
            public string Fslipf { get; set; } = "工艺卡-HWQ-NY";

            public string Fslipm { get; set; } = "工艺卡-HWQ-NY";
            public int Ftype { get; set; }     //1. 锻造 2. 金工
        }

        //-------------******************************************************************************
        [HttpGet]
        [Route("api/sli_document_mp_forge/exportpf")]
        public IHttpActionResult ExportExcelpf(int id, string Fslipf = "工艺卡-HWQ-NY")
        {
            try
            {
                // 1. 参数校验
                if (string.IsNullOrWhiteSpace(Fslipf))
                {
                    return Ok(new { code = 400, msg = "模板名称不能为空" });
                }

                // 2. 获取数据
                var context = new YourDbContext();
                var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);
                if (forgeData == null) return Ok(new { code = 404, msg = "数据不存在" });

                // 3. 构建固定模板路径
                var templatePath = Path.Combine(
                    "C:\\template", // 固定模板目录
                    $"{Fslipf.Trim()}.xlsx"
                );

                // 4. 验证模板文件存在性
                if (!File.Exists(templatePath))
                {
                    return Ok(new { code = 404, msg = "模板文件不存在", data = $"路径: {templatePath}" });
                }

                // 5. 处理Excel
                using (var workbook = new XLWorkbook(templatePath))
                {
                    var worksheet = workbook.Worksheet("Sheet1");

                    // 遍历单元格并填充数据（保持不变）
                    foreach (var cell in worksheet.CellsUsed())
                    {
                        var cellValue = cell.Value.ToString();
                        if (cellValue.StartsWith("$"))
                        {
                            var propName = cellValue.Substring(1);
                            var property = forgeData.GetType().GetProperty(propName);
                            if (property != null)
                            {
                                var value = property.GetValue(forgeData)?.ToString() ?? "";
                                cell.Value = value;
                                cell.Style.Font.Bold = false;
                            }
                        }
                    }

                    // --- 修改部分：保存到本地目录 ---
                    // 构建保存路径
                    var saveDirectory = @"C:\模拟工艺单_锻造";
                    var fileName = $"{Fslipf.Trim()}-{id}.xlsx";
                    var savePath = Path.Combine(saveDirectory, fileName);

                    // 确保目录存在
                    if (!Directory.Exists(saveDirectory))
                    {
                        Directory.CreateDirectory(saveDirectory);
                    }

                    // 保存Excel文件
                    workbook.SaveAs(savePath);

                    // 返回成功信息
                    return Ok(new
                    {
                        code = 200,
                        msg = "文件导出成功",
                        data = new
                        {
                            saved_path = savePath,
                            file_size = new FileInfo(savePath).Length
                        }
                    });
                }
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "导出失败",
                    data = ex.Message
                });
            }
        }
        //--------------------

        //-------------******************************************************************************
        [HttpGet]
        [Route("api/sli_document_mp_forge/exportpm")]
        public IHttpActionResult ExportExcelpm(int id, string Fslipm = "工艺卡-HWQ-NY")
        {
            try
            {
                // 1. 参数校验
                if (string.IsNullOrWhiteSpace(Fslipm))
                {
                    return Ok(new { code = 400, msg = "模板名称不能为空" });
                }

                // 2. 获取数据
                var context = new YourDbContext();
                var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);
                if (forgeData == null) return Ok(new { code = 404, msg = "数据不存在" });

                // 3. 构建固定模板路径
                var templatePath = Path.Combine(
                    "C:\\template", // 固定模板目录
                    $"{Fslipm.Trim()}.xlsx"
                );

                // 4. 验证模板文件存在性
                if (!File.Exists(templatePath))
                {
                    return Ok(new { code = 404, msg = "模板文件不存在", data = $"路径: {templatePath}" });
                }

                // 5. 处理Excel
                using (var workbook = new XLWorkbook(templatePath))
                {
                    var worksheet = workbook.Worksheet("Sheet1");

                    // 遍历单元格并填充数据（保持不变）
                    foreach (var cell in worksheet.CellsUsed())
                    {
                        var cellValue = cell.Value.ToString();
                        if (cellValue.StartsWith("$"))
                        {
                            var propName = cellValue.Substring(1);
                            var property = forgeData.GetType().GetProperty(propName);
                            if (property != null)
                            {
                                var value = property.GetValue(forgeData)?.ToString() ?? "";
                                cell.Value = value;
                                cell.Style.Font.Bold = false;
                            }
                        }
                    }

                    // --- 修改部分：保存到本地目录 ---
                    // 构建保存路径
                    var saveDirectory = @"C:\模拟工艺单_锻造";
                    var fileName = $"{Fslipm.Trim()}-{id}.xlsx";
                    var savePath = Path.Combine(saveDirectory, fileName);

                    // 确保目录存在
                    if (!Directory.Exists(saveDirectory))
                    {
                        Directory.CreateDirectory(saveDirectory);
                    }

                    // 保存Excel文件
                    workbook.SaveAs(savePath);

                    // 返回成功信息
                    return Ok(new
                    {
                        code = 200,
                        msg = "文件导出成功",
                        data = new
                        {
                            saved_path = savePath,
                            file_size = new FileInfo(savePath).Length
                        }
                    });
                }
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "导出失败",
                    data = ex.Message
                });
            }
        }
        //--------------------

        //-------------******************************************************************************
        [HttpGet]
        [Route("api/sli_document_mp_forge/exportcnc")]
        public IHttpActionResult ExportExcelcnc(int id, string Fslicnc = "工艺卡-HWQ-NY")
        {
            try
            {
                // 1. 参数校验
                if (string.IsNullOrWhiteSpace(Fslicnc))
                {
                    return Ok(new { code = 400, msg = "模板名称不能为空" });
                }

                // 2. 获取数据
                var context = new YourDbContext();
                var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);
                if (forgeData == null) return Ok(new { code = 404, msg = "数据不存在" });

                // 3. 构建固定模板路径
                var templatePath = Path.Combine(
                    "C:\\template", // 固定模板目录
                    $"{Fslicnc.Trim()}.xlsx"
                );

                // 4. 验证模板文件存在性
                if (!File.Exists(templatePath))
                {
                    return Ok(new { code = 404, msg = "模板文件不存在", data = $"路径: {templatePath}" });
                }

                // 5. 处理Excel
                using (var workbook = new XLWorkbook(templatePath))
                {
                    var worksheet = workbook.Worksheet("Sheet1");

                    // 遍历单元格并填充数据（保持不变）
                    foreach (var cell in worksheet.CellsUsed())
                    {
                        var cellValue = cell.Value.ToString();
                        if (cellValue.StartsWith("$"))
                        {
                            var propName = cellValue.Substring(1);
                            var property = forgeData.GetType().GetProperty(propName);
                            if (property != null)
                            {
                                var value = property.GetValue(forgeData)?.ToString() ?? "";
                                cell.Value = value;
                                cell.Style.Font.Bold = false;
                            }
                        }
                    }

                    // --- 修改部分：保存到本地目录 ---
                    // 构建保存路径
                    var saveDirectory = @"C:\模拟工艺单_锻造";
                    var fileName = $"{Fslicnc.Trim()}-{id}.xlsx";
                    var savePath = Path.Combine(saveDirectory, fileName);

                    // 确保目录存在
                    if (!Directory.Exists(saveDirectory))
                    {
                        Directory.CreateDirectory(saveDirectory);
                    }

                    // 保存Excel文件
                    workbook.SaveAs(savePath);

                    // 返回成功信息
                    return Ok(new
                    {
                        code = 200,
                        msg = "文件导出成功",
                        data = new
                        {
                            saved_path = savePath,
                            file_size = new FileInfo(savePath).Length
                        }
                    });
                }
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "导出失败",
                    data = ex.Message
                });
            }
        }
        //--------------------


        private const string TemplatePath = @"C:\template";
        private const string TemplatePathm = @"C:\templatem";
        private const string TemplatePaths = @"C:\模拟工艺单_锻造"; // 新增缺失的常量

        //------------*******************************************************8
        [HttpGet]
        public HttpResponseMessage ConvertExcelToImage(string fileName)
        {
            try
            {
                // 1. 指定本地Excel文件路径
                // 1. 安全验证（保持不变）
                if (string.IsNullOrWhiteSpace(fileName) ||
                    fileName.IndexOfAny(Path.GetInvalidFileNameChars()) >= 0 ||
                    fileName.Contains(".."))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "文件名包含非法字符");
                }

                var fworkorderPrefix = fileName.Length >= 4
                          ? fileName.Substring(0, 4)
                          : fileName.PadRight(4, '0').Substring(0, 4);

                string basePath = @"C:\模拟工艺单_锻造";
                string filePath = Path.Combine(basePath, fworkorderPrefix, fileName);


                //string filePath = Path.Combine(TemplatePaths, fileName);

                // 2. 路径安全检查（保持不变）
                if (!filePath.StartsWith(TemplatePaths, StringComparison.OrdinalIgnoreCase))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.Forbidden, "禁止访问该路径");
                }

                if (!File.Exists(filePath))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.NotFound, "文件不存在");
                }

                // 2. 转换为图片流
                var imageStream = ConvertToImage(filePath);

                // 3. 创建HTTP响应
                var response = new HttpResponseMessage(HttpStatusCode.OK);
                response.Content = new StreamContent(imageStream);
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("image/png");

                // 设置直接在浏览器中显示(而非下载)
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("inline")
                {
                    FileName = Path.GetFileNameWithoutExtension(filePath) + ".png"
                };

                return response;
            }
            catch (Exception ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

        private Stream ConvertToImage(string excelPath)
        {
            // 1. 加载工作簿
            using (Workbook workbook = new Workbook())
            {
                workbook.LoadFromFile(excelPath);
                Worksheet sheet = workbook.Worksheets[0];

                // 2. 检查数据范围
                if (sheet.LastRow < 1 || sheet.LastColumn < 1)
                    throw new Exception("Excel工作表为空或没有数据");

                // 3. 设置打印属性（关键高清配置）
                sheet.PageSetup.Orientation = PageOrientationType.Landscape; // 横向
                sheet.PageSetup.FitToPagesWide = 1;  // 限制为1页宽
                sheet.PageSetup.FitToPagesTall = 0; // 不限制页高
                sheet.PageSetup.PrintArea = sheet.Range[
                    sheet.FirstRow, sheet.FirstColumn,
                    sheet.LastRow, sheet.LastColumn].ToString(); // 精确设置打印区域

                // 4. 设置打印机级分辨率（300DPI）
                workbook.PrintDocument.DefaultPageSettings.PrinterResolution =
                    new System.Drawing.Printing.PrinterResolution()
                    {
                        X = 300,
                        Y = 300,
                        Kind = System.Drawing.Printing.PrinterResolutionKind.Custom
                    };

                // 5. 执行转换（获取原始图像）
                Image image = sheet.ToImage(
                    sheet.FirstRow, sheet.FirstColumn,
                    sheet.LastRow, sheet.LastColumn);

                // 6. 后处理增强（可选但推荐）
                Bitmap enhancedImage = new Bitmap(image.Width, image.Height);
                enhancedImage.SetResolution(300, 300); // 确保元数据DPI

                using (Graphics g = Graphics.FromImage(enhancedImage))
                {
                    g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
                    g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
                    g.DrawImage(image, 0, 0, image.Width, image.Height);
                }

                // 7. 保存为最高质量PNG
                MemoryStream ms = new MemoryStream();
                enhancedImage.Save(ms, GetPngEncoder(), GetHighQualityEncoderParams());
                ms.Position = 0;

                // 8. 释放资源
                image.Dispose();
                enhancedImage.Dispose();

                return ms;
            }
        }

        // 获取PNG编码器
        private ImageCodecInfo GetPngEncoder()
        {
            ImageCodecInfo[] codecs = ImageCodecInfo.GetImageEncoders();
            foreach (var codec in codecs)
            {
                if (codec.FormatID == ImageFormat.Png.Guid)
                    return codec;
            }
            return null;
        }

        // 设置最高质量编码参数
        private EncoderParameters GetHighQualityEncoderParams()
        {
            return new EncoderParameters(1)
            {
                Param = { [0] = new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, 100L) }
            };
        }

        //---------------------
        [HttpGet]
        //[Route("api/ConvertExcelToPdf")]
        public HttpResponseMessage ConvertExcelToPdf(string fileName, int Ftype = 0)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(fileName) ||
                    fileName.IndexOfAny(Path.GetInvalidFileNameChars()) >= 0 ||
                    fileName.Contains(".."))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "文件名包含非法字符");
                }

                var fworkorderPrefix = fileName.Length >= 4
                          ? fileName.Substring(0, 4)
                          : fileName.PadRight(4, '0').Substring(0, 4);

                string directoryPath = "";
                if (Ftype == 1)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);
                }
                else if (Ftype == 2)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                }
                else if (Ftype == 3)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_热处理", fworkorderPrefix);
                }
                else if (Ftype == 4)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_加工程序", fworkorderPrefix);
                }
                //string templatePath = Path.Combine(directoryPath, Content);

                //string basePath = @"C:\模拟工艺单_锻造";
                string filePath = Path.Combine(directoryPath, fileName);

                //string filePath = Path.Combine(TemplatePaths, fileName);

                // 2. 路径安全检查（保持不变）
                if (!filePath.StartsWith(directoryPath, StringComparison.OrdinalIgnoreCase))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.Forbidden, "禁止访问该路径");
                }

                if (!File.Exists(filePath))
                {
                    return Request.CreateErrorResponse(HttpStatusCode.NotFound, "文件不存在");
                }
                // 1. 加载Excel文件
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(filePath);




                // 2. 设置PDF转换选项（可选）
                workbook.ConverterSetting.SheetFitToPage = true; // 工作表适应页面

                // 3. 转换为PDF到内存流
                MemoryStream pdfStream = new MemoryStream();
                workbook.SaveToStream(pdfStream, FileFormat.PDF);
                pdfStream.Seek(0, SeekOrigin.Begin);

                // 4. 返回PDF文件流
                HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
                response.Content = new StreamContent(pdfStream);
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf");
                response.Content.Headers.ContentDisposition =
                    new ContentDispositionHeaderValue("inline") // 直接在浏览器中打开
                    {
                        FileName = "converted.pdf"
                    };

                return response;
            }
            catch (Exception ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

        [HttpGet]
        [Route("api/sli_document_mp_forge/exportWord")]
        public IHttpActionResult ExportWord(int id, string Ftemplatename = "工艺卡-HWQ-DOC")
        {
            try
            {
                // 1. 参数校验
                if (string.IsNullOrWhiteSpace(Ftemplatename))
                {
                    return Ok(new { code = 400, msg = "模板名称不能为空" });
                }

                // 2. 获取数据
                var context = new YourDbContext();
                var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == id);
                if (forgeData == null) return Ok(new { code = 404, msg = "数据不存在" });

                // 3. 构建固定模板路径
                var templatePath = Path.Combine(
                    "C:\\template", // 固定模板目录
                    $"{Ftemplatename.Trim()}.docx"
                );

                // 4. 验证模板文件存在性
                if (!File.Exists(templatePath))
                {
                    return Ok(new
                    {
                        code = 404,
                        msg = "模板文件不存在",
                        data = $"路径: {templatePath}"
                    });
                }

                // 5. 处理Word文档
                using (var document = DocX.Load(templatePath))
                {
                    // 使用正则表达式匹配所有$开头的占位符
                    var regex = new Regex(@"\$(\w+)");
                    var matches = regex.Matches(document.Text);
                    var placeholderNames = matches.Cast<Match>()
                        .Select(m => m.Groups[1].Value)
                        .Distinct();

                    // 动态替换占位符
                    foreach (var name in placeholderNames)
                    {
                        var property = forgeData.GetType().GetProperty(name);
                        if (property != null)
                        {
                            var value = property.GetValue(forgeData)?.ToString() ?? "";
                            document.ReplaceText($"${name}", value);
                        }
                    }

                    // 生成文件流_
                    var stream = new MemoryStream();
                    document.SaveAs(stream);
                    stream.Position = 0;

                    // 构建响应
                    var response = new HttpResponseMessage(HttpStatusCode.OK)
                    {
                        Content = new ByteArrayContent(stream.ToArray())
                    };
                    response.Content.Headers.ContentDisposition =
                        new ContentDispositionHeaderValue("attachment")
                        {
                            FileName = $"{Ftemplatename}-{id}.docx"
                        };
                    response.Content.Headers.ContentType =
                        new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.wordprocessingml.document");

                    return ResponseMessage(response);
                }
            }
            catch (Exception ex)
            {
                return Ok(new
                {
                    code = 500,
                    msg = "导出失败",
                    data = ex.Message
                });
            }
            
        }

        [HttpGet]
        public HttpResponseMessage GenerateQrCodeInExcel( string Fworkorderno = "25060291",int Ftype=0)
        {
            try
            {
                string Content = Fworkorderno+".xlsx";
                // 验证请求参数
                if (string.IsNullOrWhiteSpace(Content))
                {
                    return Request.CreateResponse(HttpStatusCode.BadRequest, "缺少二维码内容");
                }

                var fworkorderPrefix = Fworkorderno.Length >= 4
                           ? Fworkorderno.Substring(0, 4)
                           : Fworkorderno.PadRight(4, '0').Substring(0, 4);


                //string basePath = @"C:\模拟工艺单_锻造";

                string directoryPath = "";
                if (Ftype == 1)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);
                }
                else if (Ftype == 2)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                }
                else if (Ftype == 3)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_热处理", fworkorderPrefix);
                }
                else if (Ftype == 4)
                {
                    directoryPath = Path.Combine(@"C:\模拟工艺单_加工程序", fworkorderPrefix);
                }
                string templatePath = Path.Combine(directoryPath, Content);
                //string templatePath = Path.Combine(basePath, fworkorderPrefix, Content);

                // 1. 检查模板文件是否存在
                if (!File.Exists(templatePath))
                {
                    return Request.CreateResponse(HttpStatusCode.NotFound,
                        $"模板文件未找到: {templatePath}");
                }

                // 2. 生成二维码
                Bitmap qrCodeImage = GenerateQrCodeBitmap(Fworkorderno, 300, 300);

                // 3. 处理Excel文件
                using (var inputStream = new MemoryStream(File.ReadAllBytes(templatePath)))
                using (var workbook = new XLWorkbook(inputStream))
                {
                    var worksheet = workbook.Worksheet(1);

                    // 4. 插入二维码
                    using (var imageStream = new MemoryStream())
                    {
                        qrCodeImage.Save(imageStream, System.Drawing.Imaging.ImageFormat.Png);
                        imageStream.Position = 0;

                        var targetCell = worksheet.Cell("W1");
                        worksheet.AddPicture(imageStream)
                            .MoveTo(targetCell)
                            .WithSize(45, 45);
                    }

                    // ==== 关键修改：覆盖原文件 ====
                    // 5. 保存到原模板路径（覆盖）
                    using (var fileStream = new FileStream(templatePath, FileMode.Create, FileAccess.ReadWrite))
                    {
                        workbook.SaveAs(fileStream);
                    }
                }

                // 6. 返回成功响应（不再返回文件）
                return Request.CreateResponse(HttpStatusCode.OK,
                    new
                    {
                        code = 200,
                        Success = true,
                        Message = $"文件已成功覆盖到: {templatePath}",
                        FilePath = templatePath
                    });
            }
            catch (Exception ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.InternalServerError,
                    $"生成失败: {ex.Message}");
            }
        }
        /// <summary>
        /// 批量生成二维码到Excel文件
        /// </summary>
        /// <param name="Fworkorderno"></param>
        /// <returns></returns>
        [HttpPost]
        public IHttpActionResult GenerateQrCodeInExcels([FromBody] BatchExportRequest request)
        {
            try
            {
                string directoryPath = "";
                var result = new List<object>();
                if (request?.Items == null || !request.Items.Any())
                    return BadRequest("请求参数不能为空");

                using (var context = new YourDbContext())
                {
                    foreach (var item in request.Items)
                    {
                        var forgeData = context.sli_document_mp_forge.FirstOrDefault(x => x.Id == item.Id);
                        if (forgeData == null)
                        {
                            result.Add(new { Id = item.Id, Code = 404, Msg = "未找到对应数据" });
                            continue;
                        }

                        string Content = forgeData.Fworkorder + ".xlsx";


                        var fworkorderPrefix = forgeData.Fworkorder.Length >= 4
                                   ? forgeData.Fworkorder.Substring(0, 4)
                                   : forgeData.Fworkorder.PadRight(4, '0').Substring(0, 4);

                        //string basePath = @"C:\模拟工艺单_锻造";
                        
                        if (item.Ftype == 1)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_锻造", fworkorderPrefix);
                        }
                        else if (item.Ftype == 2)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_金工", fworkorderPrefix);
                        }
                        else if (item.Ftype == 3)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_热处理", fworkorderPrefix);
                        }
                        else if (item.Ftype == 4)
                        {
                            directoryPath = Path.Combine(@"C:\模拟工艺单_加工程序", fworkorderPrefix);
                        }
                        string templatePath = Path.Combine(directoryPath, Content);

                        // 1. 检查模板文件是否存在
                        if (!File.Exists(templatePath))
                        {
                            return ResponseMessage( Request.CreateResponse(HttpStatusCode.NotFound, $"模板文件未找到: {templatePath}"));
                           
                        }

                        // 2. 生成二维码
                        Bitmap qrCodeImage = GenerateQrCodeBitmap(forgeData.Fworkorder, 300, 300);

                        // 3. 处理Excel文件
                        using (var inputStream = new MemoryStream(File.ReadAllBytes(templatePath)))
                        using (var workbook = new XLWorkbook(inputStream))
                        {
                            var worksheet = workbook.Worksheet(1);

                            // 4. 插入二维码
                            using (var imageStream = new MemoryStream())
                            {
                                qrCodeImage.Save(imageStream, System.Drawing.Imaging.ImageFormat.Png);
                                imageStream.Position = 0;

                                var targetCell = worksheet.Cell("W1");
                                worksheet.AddPicture(imageStream)
                                    .MoveTo(targetCell)
                                    .WithSize(45, 45);
                            }

                            // ==== 关键修改：覆盖原文件 ====
                            // 5. 保存到原模板路径（覆盖）
                            using (var fileStream = new FileStream(templatePath, FileMode.Create, FileAccess.ReadWrite))
                            {
                                workbook.SaveAs(fileStream);
                            }
                        }

                        // 6. 返回成功响应（不再返回文件）

                        


                    }
                    return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, new
                    {
                        code = 200,
                        Success = true,
                        Message = $"文件已成功覆盖到: {directoryPath}",
                        FilePath = directoryPath
                    }));
                }
                //return Ok(result);



            }
            catch (Exception ex)
            {
                return InternalServerError(ex);
            }
        }

        // 生成二维码位图
        private Bitmap GenerateQrCodeBitmap(string content, int width, int height)
        {
            var barcodeWriter = new BarcodeWriter
            {
                Format = BarcodeFormat.QR_CODE,
                Options = new QrCodeEncodingOptions
                {
                    DisableECI = true,
                    CharacterSet = "UTF-8",
                    Width = width,
                    Height = height,
                    Margin = 1,
                    ErrorCorrection = ZXing.QrCode.Internal.ErrorCorrectionLevel.H
                }
            };

            return barcodeWriter.Write(content);
        }
    }

    // 请求模型
    public class QrCodeRequest
    {
        public string Content { get; set; } = "25060291.xlsx";       // 二维码内容
        public string TargetCell { get; set; } = "B5"; // 目标单元格（如 "B5"）
        public int Width { get; set; } = 150;     // 图片宽度
        public int Height { get; set; } = 150;    // 图片高度
        public string Fworkorderno { get; set; } = "25060291";     // 图片标题（可选）
    }



}




    








